/**
 * Provides an interface to a MySQL Database.
 * 
 * @author Chris Baillie.
 */

package database;

import java.sql.*;
import java.util.ArrayList;

public class DatabaseWrapper
{
	//Instance Vars
	String url = "jdbc:mysql://localhost/";
	String dbName = "project";
	String user = "test";
	String password = "password";
	Connection con = null;
	ResultSet rs;
	Statement stmt = null;
	
	//Create the statements we will need for speeeeed
	PreparedStatement pstmt, pstmt2, pstmt3, pstmt4, pstmt5;

	public DatabaseWrapper()
	{
		//System.err.println("Attempting to connect...");
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = DriverManager.getConnection(url+dbName,user,password);
			//System.err.println("Connected succesfully!");
			stmt = con.createStatement();
			
			} catch(Exception e){
				
				//Couldn't connect to the database.
				System.err.println("Failed to connect to the database!");
				e.printStackTrace();
			}
		
		//Prepare the prepared statements
		try {
			pstmt = con.prepareStatement("INSERT INTO Essay VALUES(null,?,?)");
			pstmt2 = con.prepareStatement("INSERT INTO Paragraph VALUES(null,?,?)");
			pstmt3 = con.prepareStatement("SELECT author FROM essay WHERE subject=?");
			pstmt4 = con.prepareStatement("SELECT content FROM paragraph WHERE eid=?");
			pstmt5 = con.prepareStatement("SELECT * FROM essay WHERE subject=? AND author=?");
		} catch(Exception e) {
			System.err.println("SQL Exception");
			e.printStackTrace();
		}
	}
	
	public int getNextEssayId()
	{
		try {
			rs = stmt.executeQuery("SELECT id FROM essay ORDER BY id DESC LIMIT 0,1");
			if(rs.next())
				return rs.getInt(1);
			else
				return 0;
		} catch (Exception e){
			System.err.println("SQL Exception");
			e.printStackTrace();
		}
		return -1;
	}
	
	public void addEssay(String subject, String author)
	{
		try {
			pstmt.setString(1,subject);
			pstmt.setString(2,author);
			pstmt.execute();
		} catch(Exception e){
			System.err.println("SQL Exception");
			e.printStackTrace();
		}
	}
	
	public void addParagraphs(ArrayList<String> paragraphs)
	{
		int eid = getNextEssayId();
		for(int i=0;i<paragraphs.size();i++){
			try {
				pstmt2.setInt(1, eid);
				pstmt2.setString(2,(String)paragraphs.get(i));
				pstmt2.execute();
			} catch (Exception e) {
				System.err.println("SQL Exception");
				e.printStackTrace();
			}
		}
		System.out.println("Paragraphs added. Go check!");
	}
	
	public ArrayList<String> getSubjects()
	{
		ArrayList<String> subjects = new ArrayList<String>();
		try {
			rs = stmt.executeQuery("SELECT DISTINCT subject FROM essay");
			while(rs.next())
				subjects.add(rs.getString(1));
		} catch (Exception e){
			//SQL Exception
			System.err.println("SQL Exception");
			e.printStackTrace();
		}
		return subjects;
	}
	
	public ArrayList<String> getEssay(String subject)
	{
		ArrayList<String> authors = new ArrayList<String>();
		try {
			//Update and Execute the query
			pstmt3.setString(1, subject);
			rs = pstmt3.executeQuery();
			while(rs.next())
				authors.add(rs.getString(1));
			return authors;
		} catch(Exception e){
			System.out.println("SQL Exception");
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<String> getWholeEssay(String subject, String author)
	{
		ArrayList<String> details = new ArrayList<String>();
		int id = 0;
		try {
			pstmt5.setString(1, subject);
			pstmt5.setString(2, author);
			rs = pstmt5.executeQuery();
			if(rs.next()){
				id = rs.getInt(1);
				details.add(rs.getString(2)); //Subject
				details.add(rs.getString(3)); //Author
				pstmt4.setInt(1, id);
				rs = pstmt4.executeQuery();
				while(rs.next())
					details.add(rs.getString(1));
			}
		} catch(Exception e){
			System.err.println("SQL Exception");
			e.printStackTrace();
		}
		
		return details;
	}
	
	public void finalize()
	{
		try {
			con.close();
		} catch(Exception e){
			System.err.println("SQL Exception");
			e.printStackTrace();
		}
	}
}
